COUNT(*) vs COUNT(PK): Which One Is More Efficient?

It’s a great question and the answer reveals an important distinction in SQL Server.

Think of it like this:

• COUNT(*) is like asking: “How many chairs are in the room?” You count every chair, regardless of whether someone is sitting on it.
• COUNT(field) is like asking: “How many chairs are actually occupied?” You only count the chairs that aren't empty.

• Counts all rows in the table, no matter what values the columns hold.
• Even if some columns contain NULL, those rows are still included.
• It's the fastest and least expensive way to count rows because:
  • The database engine doesn't need to read any specific column values.
  • It only checks whether a row exists.
  • With a proper index (like a clustered index), SQL Server can count rows by scanning the index structure without touching the actual data pages.

• Counts only rows where the specified column is not NULL.
• This requires more work:
  • The engine must read the column value to check for NULL.
  • If the column is NULL, that row is skipped.
  • This means the engine has to access the data pages.
  • Without an index on that column, SQL Server may need to perform a full table scan.

If your goal is simply to know how many rows exist in a table, COUNT(*) is the most efficient and cost‑effective choice.
Use COUNT(field) only when you specifically need to count non‑NULL values in a particular column.